﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Xml;

namespace WorkRecord.CommonClass
{
    public class DataAccess
    {
        string connstr = System.Configuration.ConfigurationManager.AppSettings["DbConnectionStirng"];

        public DataTable readdata(string selstr)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
                try
                {
                    conn.Open();
                    SqlDataAdapter sda = new SqlDataAdapter(selstr, conn);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    sda.Dispose();
                    conn.Close();
                    return dt;
                }
                catch (Exception err) { throw err; }
        }

        public void datacmd(string cmdstr)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
                try
                {
                    conn.Open();
                    SqlCommand sc = new SqlCommand(cmdstr, conn);
                    sc.ExecuteNonQuery();
                    sc.Dispose();
                    conn.Close();
                }
                catch (Exception err) { throw err; }
        }

        public void datacmd(string cmdstr, SqlParameter[] sp)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
                try
                {
                    conn.Open();
                    SqlCommand sc = new SqlCommand(cmdstr, conn);
                    sc.Parameters.AddRange(sp);
                    sc.ExecuteNonQuery();
                    sc.Dispose();
                    conn.Close();
                }
                catch (Exception err) { throw err; }
        }

        public bool CanEnter(string user,out DataTable dt)
        {
            var sql = string.Format(@"  select * from JourneyCategoryUserMapping
  INNER JOIN JourneyCategory ON JourneyCategoryUserMapping.CategoryID=JourneyCategory.ID
  WHERE JourneyCategoryUserMapping.UserAccount='{0}'", user);
            dt = readdata(sql);
            return dt != null && dt.Rows.Count > 0;
        }
        public bool CanEnter(string user,string categoryID, out DataTable dt)
        {
            var sql = string.Format(@"  select * from JourneyCategoryUserMapping
  INNER JOIN JourneyCategory ON JourneyCategoryUserMapping.CategoryID=JourneyCategory.ID
  WHERE JourneyCategoryUserMapping.UserAccount='{0}' {1}", user,
                string.IsNullOrEmpty(categoryID)
                    ? string.Empty
                    : string.Format("and JourneyCategoryUserMapping.CategoryID='{0}'", categoryID));
            dt = readdata(sql);
            return dt != null && dt.Rows.Count > 0;
        }

        public DataTable GetMajorWorj(string user)
        {
            return
                readdata(string.Format("select * from JourneyMajorWork where UserAccount='{0}' order by SubmitTime desc",
                    user));
        }
    }
}